﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data;
using Pixysoft.Framework.XSchEmA.Aspect;

namespace Pixysoft.Framework.XSchEmA.Database
{
    class MySql4xSchemaDownloader
    {
        List<string> systblist = new List<string>();

        private static volatile MySql4xSchemaDownloader instance;

        private static object syncRoot = new Object();

        public static MySql4xSchemaDownloader Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        instance = new MySql4xSchemaDownloader();
                    }
                }

                return instance;

            }
        }

        public MySql4xSchemaDownloader()
        {
            systblist.Add("COLUMNS_PRIV");
            systblist.Add("DB");
            systblist.Add("FUNC");
            systblist.Add("HELP_CATEGORY");
            systblist.Add("HELP_KEYWORD");
            systblist.Add("HELP_RELATION");
            systblist.Add("HELP_TOPIC");
            systblist.Add("HOST");
            systblist.Add("PROC");
            systblist.Add("PROCS_PRIV");
            systblist.Add("TABLES_PRIV");
            systblist.Add("TIME_ZONE");
            systblist.Add("TIME_ZONE_LEAP_SECOND");
            systblist.Add("TIME_ZONE_NAME");
            systblist.Add("TIME_ZONE_TRANSITION");
            systblist.Add("TIME_ZONE_TRANSITION_TYPE");
            systblist.Add("USER");
        }

        public void GetTables(string databasename, MySqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Tables", new string[] { null, databasename });

                foreach (DataRow row in table.Rows)
                {
                    string tabletype = row["TABLE_TYPE"].ToString().Trim().ToUpper();

                    string tablename = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    if (tabletype == "SYSTEM VIEW")
                        continue;

                    if (systblist.Contains(tablename))
                        continue;

                    TableSchema tableSchema = new TableSchema(tablename);
                    tableSchema.Comment = row["TABLE_COMMENT"].ToString();
                    schema.Tables.Add(tableSchema);
                }
            }
        }

        public void GetColumns(string databasename, MySqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Columns", new string[] { null, databasename });

                foreach (TableSchema tbschema in schema.Tables)
                {
                    foreach (DataRow row in Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLE_NAME", tbschema.TableName))
                    {
                        string typename = row["DATA_TYPE"].ToString().Trim().ToUpper();

                        string colname = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                        string defaultValue = row["COLUMN_DEFAULT"].ToString().Trim().ToUpper();

                        string extra = row["EXTRA"].ToString().Trim().ToUpper();


                        ColumnSchema columnSchema = new ColumnSchema();
                        columnSchema.TableName = tbschema.TableName;
                        columnSchema.ColumnName = colname;
                        columnSchema.IsNullable = row["IS_NULLABLE"].ToString().Trim() == "YES" ? true : false;
                        columnSchema.DefaultValue = defaultValue == "" ? null : defaultValue;
                        columnSchema.Comment = row["COLUMN_COMMENT"].ToString();


                        DataTypeSchema typeschema = new DataTypeSchema(typename,
                          row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "" ? Int64.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim()) : -1,
                          row["NUMERIC_PRECISION"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_PRECISION"].ToString().Trim()) : -1,
                          row["NUMERIC_SCALE"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_SCALE"].ToString().Trim()) : -1,
                          extra == "AUTO_INCREMENT" ? true : false);

                        switch (Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(typename))
                        {
                            case MySqlDataTypeCollection.YEAR:// year is int
                            case MySqlDataTypeCollection.BOOL:
                            case MySqlDataTypeCollection.BOOLEAN:
                            case MySqlDataTypeCollection.TINYINT:
                            case MySqlDataTypeCollection.SMALLINT:
                            case MySqlDataTypeCollection.MEDIUMINT:
                            case MySqlDataTypeCollection.INT:
                            case MySqlDataTypeCollection.INTEGER:
                            case MySqlDataTypeCollection.BIGINT:
                            case MySqlDataTypeCollection.FLOAT:
                            case MySqlDataTypeCollection.DOUBLE:
                            case MySqlDataTypeCollection.DOUBLE_PRECISION:
                            case MySqlDataTypeCollection.REAL:
                            case MySqlDataTypeCollection.DECIMAL:
                            case MySqlDataTypeCollection.DEC:
                            case MySqlDataTypeCollection.FIXED:
                            case MySqlDataTypeCollection.NUMERIC:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = -1;
                                    typeschema.Scale = -1;

                                    string coltype = row["COLUMN_TYPE"].ToString().ToUpper().Replace(typename, "").Replace("(", "").Replace(")", "");

                                    if (string.IsNullOrEmpty(coltype))
                                    {
                                        // do nothing
                                    }
                                    else
                                    {
                                        string[] numerics = coltype.Split(',');

                                        typeschema.Precision = int.Parse(numerics[0]);

                                        if (numerics.Length > 1)
                                        {
                                            typeschema.Scale = int.Parse(numerics[1]);
                                        }
                                    }

                                    break;
                                }


                            case MySqlDataTypeCollection.BIT:
                            case MySqlDataTypeCollection.DATE:
                            case MySqlDataTypeCollection.DATETIME:
                            case MySqlDataTypeCollection.TIMESTAMP:
                            case MySqlDataTypeCollection.TIME:
                                {
                                    // do nothing
                                    break;
                                }


                            case MySqlDataTypeCollection.CHAR:
                            case MySqlDataTypeCollection.VARCHAR:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = -1;
                                    typeschema.Scale = -1;

                                    string coltype = row["COLUMN_TYPE"].ToString().ToUpper().Replace(typename, "").Replace("(", "").Replace(")", "");

                                    if (string.IsNullOrEmpty(coltype))
                                    {
                                        // do nothing
                                    }
                                    else
                                    {
                                        string[] numerics = coltype.Split(',');

                                        typeschema.Length = int.Parse(numerics[0]);
                                    }

                                    break;
                                }

                            case MySqlDataTypeCollection.VARBINARY:
                            case MySqlDataTypeCollection.BINARY:
                            case MySqlDataTypeCollection.TINYBLOB:
                            case MySqlDataTypeCollection.TINYTEXT:
                            case MySqlDataTypeCollection.BLOB:
                            case MySqlDataTypeCollection.TEXT:
                            case MySqlDataTypeCollection.MEDIUMBLOB:
                            case MySqlDataTypeCollection.MEDIUMTEXT:
                            case MySqlDataTypeCollection.LONGBLOB:
                            case MySqlDataTypeCollection.LONGTEXT:
                            case MySqlDataTypeCollection.ENUM:
                            case MySqlDataTypeCollection.SET:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Precision = -1;
                                    typeschema.Scale = -1;
                                    break;
                                }
                            default:
                                break;
                        }

                        columnSchema.DataType = typeschema;

                        tbschema.Columns.Add(columnSchema);
                    }
                }

            }
        }

        internal void GetConstraints(string databasename, MySqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Indexes", new string[] { null, databasename });

                foreach (TableSchema tbschema in schema.Tables)
                {
                    DataRow[] rows = Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLE_NAME", tbschema.TableName);

                    foreach (DataRow row in rows)
                    {
                        bool unique = bool.Parse(row["UNIQUE"].ToString());

                        bool primarykey = bool.Parse(row["PRIMARY"].ToString());

                        string constname = row["INDEX_NAME"].ToString().ToUpper();

                        if (unique && primarykey)
                        {
                            //primary key

                            PrimaryKeySchema pkSchema = new PrimaryKeySchema();
                            pkSchema.ConsName = constname;
                            pkSchema.IndexName = constname;
                            pkSchema.TableName = tbschema.TableName;
                            schema.PrimaryKeys.Add(pkSchema);
                        }
                        else if (unique)
                        {
                            //unique

                            UniqueSchema uSchema = new UniqueSchema();
                            uSchema.ConsName = constname;
                            uSchema.IndexName = constname;
                            uSchema.TableName = tbschema.TableName;
                            schema.Uniques.Add(uSchema);
                        }
                        else
                        {
                            //index 

                            IndexSchema iSchema = new IndexSchema();
                            iSchema.ConsName = constname;
                            iSchema.IndexName = constname;
                            iSchema.TableName = tbschema.TableName;
                            schema.Indexs.Add(iSchema);
                        }
                    }
                }

                table = conn.GetSchema("IndexColumns", new string[] { null, databasename });

                foreach (DataRow row in table.Rows)
                {
                    string tbname = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    string constName = row["INDEX_NAME"].ToString().ToUpper().Trim();

                    string columnName = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                    int index = int.Parse(row["ORDINAL_POSITION"].ToString().ToUpper().Trim()) - 1;

                    if (systblist.Contains(tbname))
                        continue;

                    PrimaryKeySchema pkSchema = schema.GetPrimaryKeyByName(tbname, constName);

                    if (pkSchema != null)
                    {
                        SchemaUtility.AddToList<string>(pkSchema.ColumnNames, columnName, index);

                        continue;
                    }

                    UniqueSchema uSchema = schema.GetUniqueByName(constName);

                    if (uSchema != null)
                    {
                        SchemaUtility.AddToList<string>(uSchema.ColumnNames, columnName, index);

                        continue;
                    }

                    IndexSchema iSchema = schema.GetIndexByName(constName);

                    if (iSchema != null)
                    {
                        SchemaUtility.AddToList<string>(iSchema.ColumnNames, columnName, index);

                        continue;
                    }
                }
            }
        }

        internal void GetForeignKeys(string databasename, DatabaseType type, MySqlConnection oconn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((oconn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    oconn.Open();
                }

                List<string> rcollist = new List<string>();
                string rtable = null;
                ForeignKeySchema formerschema = null;

                DataTable table = oconn.GetSchema("Foreign Key Columns", new string[] { null, databasename });

                foreach (DataRow row in table.Rows)
                {
                    string constname = row["CONSTRAINT_NAME"].ToString().ToUpper().Trim();

                    string ftablename = row["TABLE_NAME"].ToString().Trim().ToUpper();

                    string rtablename = row["REFERENCED_TABLE_NAME"].ToString().Trim().ToUpper();

                    //09-10-27 4.x版本会多无聊的问题

                    string fcolname = row["COLUMN_NAME"].ToString().Trim().ToUpper().Replace("`", "");

                    string rcolname = row["REFERENCED_COLUMN_NAME"].ToString().Trim().ToUpper().Replace("`", "");

                    //09-10-27 在4.0版本是0开始的序列

                    int colindex = 0;
                    if (type == DatabaseType.MySql)
                        colindex = int.Parse(row["ORDINAL_POSITION"].ToString().Trim().ToUpper()) - 1;
                    else
                        colindex = int.Parse(row["ORDINAL_POSITION"].ToString().Trim().ToUpper());



                    ForeignKeySchema fkschema = schema.GetForeignKeyByName(constname);

                    if (fkschema == null)
                    {
                        if (rcollist.Count > 0 && formerschema != null)
                        {
                            IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                            if (constschema != null)
                            {
                                string pkconstname = constschema.ConsName;
                                formerschema.PkeyConsName = pkconstname;


                                //2010-08-01 支持最新的fk结构

                                formerschema.PkTableName = constschema.TableName;
                                foreach (string column in constschema.ColumnNames)
                                    formerschema.PkColumnNames.Add(column);
                            }
                        }

                        fkschema = new ForeignKeySchema();
                        schema.ForeignKeys.Add(fkschema);
                        rcollist = new List<string>();
                        rtable = null;
                        formerschema = null;
                    }

                    fkschema.ConsName = constname;
                    fkschema.FkTableName = ftablename;
                    SchemaUtility.AddToList<string>(fkschema.FkColumnNames, fcolname, colindex);
                    SchemaUtility.AddToList<string>(rcollist, rcolname, colindex);
                    rtable = rtablename;
                    formerschema = fkschema;
                }

                if (rcollist.Count > 0 && formerschema != null)
                {
                    IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                    if (constschema != null)
                    {
                        string pkconstname = constschema.ConsName;
                        formerschema.PkeyConsName = pkconstname;


                        //2010-08-01 支持最新的fk结构

                        formerschema.PkTableName = constschema.TableName;
                        foreach (string column in constschema.ColumnNames)
                            formerschema.PkColumnNames.Add(column);
                    }
                }

                if (oconn.State == ConnectionState.Open)
                    oconn.Close();
            }
        }
    }
}
